package cn.com.elite;

import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.dom4j.Document;
import org.dom4j.DocumentFactory;
import org.dom4j.Element;

import com.elitecrm.saas.service.EliteSAASException;

import java.sql.Timestamp;
import java.sql.Types; 

public class GetPXListService{
	private static final Log log = LogFactory.getLog(GetPXListService.class);
	
	public String getPXListService(DataSource dataSource,String ep_id,Document doc,String staffid)throws Exception{
		String ReturnXml="";
		Element TypeEle=(Element)doc.selectSingleNode("/Root/Type");
		if(TypeEle!=null){
			String Type=TypeEle.getText();
			if(Type!=null){
				if(Type.equals("ks")){
					ReturnXml=getKSListServiceImpl(dataSource,ep_id,staffid);
				}else if(Type.equals("px")){
					ReturnXml=getPXListServiceImpl(dataSource,ep_id,staffid);
				}else{
					throw new EliteSAASException(EliteSAASException.INVAILD_PARAMETER,"wrong type");
				}
			}else{
				throw new EliteSAASException(EliteSAASException.INVAILD_PARAMETER,"TypeEle.getText()==null");
			}
		}else{
			throw new EliteSAASException(EliteSAASException.INVAILD_PARAMETER,"TypeEle==null");
		}
		return ReturnXml;
	}
	public String getPXListServiceImpl(DataSource dataSource,String ep_id,String staffid)throws Exception{
		String ReturnXml=""; 
		String W_CoursewareConfig="W_CoursewareConfig_"+ep_id;
		String W_CourseSchemFolder="W_CourseSchemFolder_"+ep_id;
		String W_SchemeRelCourse="W_SchemeRelCourse_"+ep_id;
		String W_CourseConfig="W_CourseConfig_"+ep_id;
		String W_CourseScheme="W_CourseScheme_"+ep_id;
		String W_SchemeRelBy="W_SchemeRelBy_"+ep_id;
		String W_CoursewareFileType="W_CoursewareFileType";
		String sql="select "+W_CoursewareConfig+".CoursewareName,"+W_CoursewareConfig+".CoursewareDesc,FileTypeDesc,"+W_CourseSchemFolder+".FolderID," +
				""+W_SchemeRelCourse+".SchemeID,"+W_CoursewareConfig+".CoursewareID,"+W_CourseConfig+".CourseID " +
				"from "+W_CoursewareConfig+" " +
				"left join "+W_CoursewareFileType+" on "+W_CoursewareFileType+".FileTypeID="+W_CoursewareConfig+".FileType " +
				"left join "+W_CourseConfig+" on "+W_CoursewareConfig+".CourseID="+W_CourseConfig+".CourseID " +
				"left join "+W_SchemeRelCourse+" on "+W_SchemeRelCourse+".CoursewareID="+W_CoursewareConfig+".CoursewareID " +
				"left join "+W_CourseScheme+" on "+W_CourseScheme+".SchemeID="+W_SchemeRelCourse+".SchemeID " +
				"left join "+W_CourseSchemFolder+" on "+W_CourseScheme+".FolderID="+W_CourseSchemFolder+".FolderID " +
				"left join "+W_SchemeRelBy+" on "+W_CourseScheme+".SchemeID="+W_SchemeRelBy+".SchemeID"+
				" where "+W_SchemeRelBy+".HandleBy_ID=?";
		log.debug("getPXListServiceImpl_sql:"+sql);
		Object[] params=new Object[]{staffid};  
		int[] types=new int[]{Types.VARCHAR};  
		List KSList=util.QueryPersonUseExecute(sql,types,params,dataSource);
		Document result=util.getDoc();
		Element root=result.addElement("R");
		root.addElement("Result").addText("1");
		if(KSList!=null&&KSList.size()>0){
			root.addElement("PXListLEN").addText(KSList.size()+"");
	    	for(int i=0;i<KSList.size();i++){
	    		Map map=(Map)KSList.get(i);
	    		Element kslist=root.addElement("PXList");
	    		
	    		String CoursewareName=(String)map.get("CoursewareName");
	    		log.debug("getPXList_CoursewareName:"+CoursewareName);
	    		kslist.addElement("CoursewareName").addText(CoursewareName);
	    		
	    		String CoursewareDesc=(String)map.get("CoursewareDesc");
	    		log.debug("getPXList_CoursewareDesc:"+CoursewareDesc);
	    		kslist.addElement("CoursewareDesc").addText(CoursewareDesc);
	    		
	    		String FileTypeDesc=(String)map.get("FileTypeDesc");
	    		log.debug("getPXList_FileTypeDesc:"+FileTypeDesc);
	    		kslist.addElement("FileTypeDesc").addText(FileTypeDesc);
	    		
	    		String FolderID=(String)map.get("FolderID");
	    		log.debug("getPXList_FolderID:"+FolderID);
	    		kslist.addElement("FolderID").addText(FolderID);
	    		
	    		String SchemeID=(String)map.get("SchemeID");
	    		log.debug("getPXList_SchemeID:"+SchemeID); 
	    		kslist.addElement("SchemeID").addText(SchemeID);
	    		
	    		String CoursewareID=(String)map.get("CoursewareID");
	    		log.debug("getPXList_CoursewareID:"+CoursewareID);
	    		kslist.addElement("CoursewareID").addText(CoursewareID);
	    		
	    		String CourseID=(String)map.get("CourseID");
	    		log.debug("CourseID:"+CourseID);
	    		kslist.addElement("CourseID").addText(CourseID);
	    	}
		}else{
			root.addElement("PXListLEN").addText("0");
		}
		ReturnXml=result.asXML();
		log.debug("getPXListServiceImpl_ReturnXml"+ReturnXml);
		return ReturnXml;
		
	}
	public String getKSListServiceImpl(DataSource dataSource,String ep_id,String staffid)throws Exception{
		SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String ReturnXml=""; 
		String W_ExamScheme="W_ExamScheme_"+ep_id;
		String W_SchemeExamRelBy="W_SchemeExamRelBy_"+ep_id;
		String W_ExamMain="W_ExamMain_"+ep_id;
		String sql="select "+W_ExamScheme+".SchemeID, "+W_ExamScheme+".ExamMainID,"+W_ExamScheme+".SchemeName,ExamMainName,SchemeDesc,VaildBegin,VaildEnd,VaildDateFlag,InType,ExamTime from "+W_SchemeExamRelBy+
				" left join "+W_ExamScheme+" on "+W_SchemeExamRelBy+".SchemeID="+W_ExamScheme+".SchemeID " +
				" left join "+W_ExamMain+" on "+W_ExamScheme+".ExamMainID="+W_ExamMain+".ExamMainID "+
				" where "+W_SchemeExamRelBy+".HandleBy_ID=?";
		log.debug("getKSListServiceImpl_sql:"+sql);
		Object[] params=new Object[]{staffid};  
		int[] types=new int[]{Types.VARCHAR};  
		List KSList=util.QueryPersonUseExecute(sql,types,params,dataSource);
		Document result=util.getDoc();
		Element root=result.addElement("R");
		root.addElement("Result").addText("1");
		if(KSList!=null&&KSList.size()>0){
			root.addElement("KSListLEN").addText(KSList.size()+"");
	    	for(int i=0;i<KSList.size();i++){
	    		Map map=(Map)KSList.get(i);
	    		Element kslist=root.addElement("KSList");
	    		
	    		String SchemeID=(String)map.get("SchemeID");
	    		log.debug("getKSList_SchemeID:"+SchemeID);
	    		kslist.addElement("SchemeID").addText(SchemeID);
	    		
	    		String ExamMainID=(String)map.get("ExamMainID");
	    		log.debug("getKSList_ExamMainID:"+ExamMainID);
	    		kslist.addElement("ExamMainID").addText(ExamMainID);
	    		
	    		String SchemeName=(String)map.get("SchemeName");
	    		log.debug("getKSList_SchemeName:"+SchemeName);
	    		kslist.addElement("SchemeName").addText(SchemeName);
	    		
	    		String ExamMainName=(String)map.get("ExamMainName");
	    		log.debug("getKSList_ExamMainName:"+ExamMainName);
	    		kslist.addElement("ExamMainName").addText(ExamMainName);
	    		
	    		String SchemeDesc=(String)map.get("SchemeDesc");
	    		log.debug("SchemeDesc:"+SchemeDesc);
	    		kslist.addElement("SchemeDesc").addText(SchemeDesc);
	    		
	    		Timestamp VaildBegin1=(Timestamp)map.get("VaildBegin");
	    		String VaildBegin=df.format(VaildBegin1);
	    		log.debug("VaildBegin:"+VaildBegin);
	    		kslist.addElement("VaildBegin").addText(VaildBegin);
	    		
	    		Timestamp VaildEnd1=(Timestamp)map.get("VaildEnd");
	    		String VaildEnd=df.format(VaildEnd1);
	    		log.debug("VaildEnd:"+VaildEnd);
	    		kslist.addElement("VaildEnd").addText(VaildEnd);
	    		
	    		String VaildDateFlag=Integer.toString((Integer)map.get("VaildDateFlag"));
	    		log.debug("VaildDateFlag:"+VaildDateFlag);
	    		kslist.addElement("VaildDateFlag").addText(VaildDateFlag);
	    		
	    		String InType=Integer.toString((Integer)map.get("InType"));
	    		log.debug("InType:"+InType);
	    		kslist.addElement("InType").addText(InType);
	    		
	    		String ExamTime=Integer.toString((Integer)map.get("ExamTime"));
	    		log.debug("ExamTime:"+ExamTime);
	    		kslist.addElement("ExamTime").addText(ExamTime);
	    	}
		}else{
			root.addElement("KSListLEN").addText("0");
		}
		ReturnXml=result.asXML();
		log.debug("getKSListServiceImpl_ReturnXml"+ReturnXml);
		return ReturnXml;
	}

}
